﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HtmlAgilityPack;
using System.Net;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Xml;

namespace AsianSocietyPictureUpload
{
    public partial class AsianSocietyUploadPicturesToDatabase
    {
        private void GetPollutionData()
        {

            foreach (DataRow row in dtCoord.Rows)
            {
                try
                {
                    int CityHour = 0;
                    int CityID = Convert.ToInt32(row["cityID"]);

                    CityHour = Convert.ToInt32(row["timezone"]);

                    DateTime currentTime = DateTime.UtcNow.AddHours(CityHour);


                    if (!CheckExitData(CityID) && currentTime.Hour >= 10)
                     {
                        switch (CityID)
                        {
                            case 3:
                                string BeijingAPI = "";//GetBeijingAPI();
                                string BeijingAQI = "";
                                string BeijingUSPM25 = GetBeijingUSPM25("https://api.twitter.com/1/statuses/user_timeline.xml?screen_name=BeijingAir", ref BeijingAQI);
                                string Beijing10 = "";//GetBeijingPM10();
                                string BeijingPM25 = "";// GetBeijingPM25();
                                
                                InsertData(CityID, BeijingAQI, BeijingAPI, BeijingPM25, Beijing10, BeijingUSPM25, "0");
                                break;
                            case 4:
                                string GaungzhouAQI = "";
                                string GuangzhouUSPM25 = GetBeijingUSPM25("https://api.twitter.com/1/statuses/user_timeline.xml?screen_name=Guangzhou_Air", ref GaungzhouAQI);
                                string GuangzhouAPI = "";// GetGuangzhouAPI();
                                string GuangzhouPM25 = "";//GetGuangzhouPM25();
                                InsertData(CityID, GaungzhouAQI, GuangzhouAPI, GuangzhouPM25, "0", GuangzhouUSPM25, "0");
                                break;
                            case 6:
                                string HongKongAPI = "";//GetHongKongAPI();
                                string HongKongPM10 = "";//GetHongKongPM10();
                                InsertData(CityID, "0", HongKongAPI, "0", HongKongPM10, "0", "0");
                                break;


                            case 5:     //Shanghai
                                string ShanghaiAQI = "";
                                string ShanghaiUSPM25 = GetBeijingUSPM25("https://api.twitter.com/1/statuses/user_timeline.xml?screen_name=cgshanghaiair", ref ShanghaiAQI);
                                string ShanghaiAPI = "";//GetShanghaiAPI();
                                InsertData(CityID, ShanghaiAQI, ShanghaiAPI, "0", "0", ShanghaiUSPM25, "0");
                                break;
                            default:
                                break;
                        }

                    }
                }
                catch (Exception e) { Console.Write(e.Message); }
            }
        }
     
        private string GetBeijingUSPM25(string path, ref string AQI)
        {
            string URL = path;
            XmlDocument xDoc = new XmlDocument();
            xDoc.Load(URL);



            XmlNodeList nodes = xDoc.SelectNodes("/statuses/status/*");

            string pm25=string.Empty;

            string[] titleText = nodes[2].InnerText.Split(';');
            
            
            pm25 = titleText[2];
            AQI = titleText[3];


            return pm25;
        }

        private string GetGuangzhouAPI()
        {
            string result = string.Empty;

            string Url = "http://datacenter.mep.gov.cn/report/airAction.do?city=%B9%E3%D6%DD&startdate=2012-04-30&location=rq";

            WebClient client = new WebClient();
            string htmlWhole = client.DownloadString(Url);

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);

            request.Method = "GET";
            using (var stream = request.GetResponse().GetResponseStream())

            using (var reader = new StreamReader(stream, Encoding.UTF8))
                result = reader.ReadToEnd();

            HtmlDocument doc = new HtmlDocument();

            doc.Load(new StringReader(result));

            HtmlNode root = doc.DocumentNode;

            List<string> anchorTags = new List<string>();


            foreach (HtmlNode link in root.SelectNodes("//td"))
            {
                string att = link.InnerHtml;
                anchorTags.Add(att);
            }

            if (anchorTags.Count > 0)
            {
                char[] delimiterChars = { '>', '<', '&', '\t', '\r', '\n' };
                string[] SAPI = anchorTags[1].Split(delimiterChars);
                return SAPI[53].Trim();
            }

            return "";
        }

        private string GetShanghaiAPI()
        {

            string result = string.Empty;

            string Url = "http://datacenter.mep.gov.cn/report/airAction.do?city=%C9%CF%BA%A3&startdate=2012-04-30&location=rq";

            WebClient client = new WebClient();
            string htmlWhole = client.DownloadString(Url);

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);

            request.Method = "GET";
            using (var stream = request.GetResponse().GetResponseStream())

            using (var reader = new StreamReader(stream, Encoding.UTF8))
                result = reader.ReadToEnd();

            HtmlDocument doc = new HtmlDocument();

            doc.Load(new StringReader(result));

            HtmlNode root = doc.DocumentNode;

            List<string> anchorTags = new List<string>();


            foreach (HtmlNode link in root.SelectNodes("//td"))
            {
                string att = link.InnerHtml;
                anchorTags.Add(att);
            }

            if (anchorTags.Count > 0)
            {
                char[] delimiterChars = { '>', '<', '&', '\t', '\r', '\n' };
                string[] SAPI = anchorTags[1].Split(delimiterChars);
                return SAPI[53].Trim();
            }

            return "";
        }

        private bool CheckExitData(int CityID)
        {
            string sqlCommand = "Select sum(pollutionCN8+pollutionUS8+pollutionCN7 + pollutionUS7 + pollutionCN6 + pollutionUS6) from chinaair.tb_city_pollution where zdate = '" + DateTime.Now.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) + "' and fcityid=" + CityID;

            int mid=0;

            try
            {
                mid = MidReader(sqlCommand);
            }
            catch (Exception e)
            {
            }
            if (mid != 0)
               return true;
            else
               return false;
        }

        private int CheckExitDataMid(int CityID)
        {
            string sqlCommand = "Select mid from chinaair.tb_city_pollution where zdate = '" + DateTime.Now.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) + "' and fcityid=" + CityID;

            int mid = MidReader(sqlCommand);

            return mid;
        }

        private void InsertData(int CityID, string USAQI , string CNAPI , string CNPM25, string CNPM10 , string USPM25, string USPM10)
        {
            int ChMID = CheckExitDataMid(CityID);
            if (ChMID == 0)
            {
                AsiaDBTableAdapters.tb_city_pollutionTableAdapter cityPolluA = new AsiaDBTableAdapters.tb_city_pollutionTableAdapter();

                cityPolluA.Insert("N",
                                    DateTime.Now.Date,
                                    CityID,
                                    CNPM10,
                                    USPM10,
                                    CNAPI,
                                    USAQI,
                                    CNPM25,
                                    USPM25,
                                    "",
                                    "",
                                    "",
                                    3,
                                    1,
                                    DateTime.Now.Date,
                                    5,
                                    DateTime.Now.Date,
                                    0,
                                    0);
            }
            else
            {
                MySqlConnection mySQLCon = new MySqlConnection(setString.chinaairConnectionString);
                string sqlComand = "UPDATE chinaair.tb_city_pollution  SET  pollutionCN8=" + CNPM10 + ", pollutionUS8 =" + USPM10 + ", pollutionCN7=" + CNAPI + ", pollutionUS7=" + USAQI + ",pollutionCN6="+CNPM25 + ",pollutionUS6=" + USPM25 +" WHERE MID =" + ChMID;

          

       
                MySqlCommand updateMainImage = new MySqlCommand(sqlComand);

                updateMainImage.Connection = mySQLCon;
                mySQLCon.Open();
                updateMainImage.ExecuteNonQuery();
                mySQLCon.Close();
            }
        }

        private string GetLosAngelesAPI()
        {
            int Count = -1;
            string result = string.Empty;
            

            string Url = "http://www.airnow.gov/index.cfm?action=airnow.local_city&cityid=342";

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);

            request.Method = "GET";
            using (var stream = request.GetResponse().GetResponseStream())

            using (var reader = new StreamReader(stream, Encoding.UTF8))
            {
                result = reader.ReadToEnd();
            }

            HtmlDocument doc = new HtmlDocument();

            doc.Load(new StringReader(result));

            HtmlNode root = doc.DocumentNode;

            List<string> anchorTags = new List<string>();


            foreach (HtmlNode link in root.SelectNodes("//td"))
            {
                string att = link.InnerHtml;
                anchorTags.Add(att);
            }
          
            
            string compareString = "Current Conditions";

            for (int i = 0; i < anchorTags.Count; i++)
            {

                char[] delimiterChars = { '<', '>' };

                string[] LaPM25 = anchorTags[i].Split(delimiterChars);

                for (int k = 0; k < LaPM25.Length; k++)
                {

                    string trimString = LaPM25[k].Trim();

                    if (trimString.Equals(compareString))
                    {
                        Count = i;
                    }
                }
            }



            if (anchorTags.Count > -1)
            {
                char[] delimiterChars = { '>', '<', '&', '\r','\t', '\n' };

                string[] LaPM25 = anchorTags[Count + 5].Split(delimiterChars);

                return LaPM25[4].Trim();
            }

            return ""; 
        }

        private string GetNewYorkPM25()
        {
            string result = string.Empty;

            string Url = "http://www.dec.ny.gov/airmon/stationStatus.php?stationNo=60";

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);

            request.Method = "GET";
            using (var stream = request.GetResponse().GetResponseStream())

            using (var reader = new StreamReader(stream, Encoding.UTF8))
                result = reader.ReadToEnd();

            HtmlDocument doc = new HtmlDocument();

            doc.Load(new StringReader(result));

            HtmlNode root = doc.DocumentNode;

            List<string> anchorTags = new List<string>();


            foreach (HtmlNode link in root.SelectNodes("//td"))
            {
                string att = link.InnerHtml;
                anchorTags.Add(att);
            }

            if (anchorTags.Count > 0)
            {
                char[] delimiterChars = { '>', '<', '&'};
                string[] BAPI = anchorTags[16].Split(delimiterChars);
                return BAPI[0].Trim();
            }

            return "";
        }

        private string GetNewYorkAPI()
        {
            string result = string.Empty;

            string Url = "http://www.dec.ny.gov/airmon/stationStatus.php?stationNo=60";

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);

            request.Method = "GET";
            using (var stream = request.GetResponse().GetResponseStream())

            using (var reader = new StreamReader(stream, Encoding.UTF8))
                result = reader.ReadToEnd();

            HtmlDocument doc = new HtmlDocument();

            doc.Load(new StringReader(result));

            HtmlNode root = doc.DocumentNode;

            List<string> anchorTags = new List<string>();


            foreach (HtmlNode link in root.SelectNodes("//td"))
            {
                string att = link.InnerHtml;
                anchorTags.Add(att);
            }

            if (anchorTags.Count > 0)
            {
                char[] delimiterChars = { '>', '<',';' };
                string[] BAPI = anchorTags[12].Split(delimiterChars);
                return BAPI[1].Trim();
            }

            return "";
        }

        private string GetBeijingPM10()
        {
            try
            {
                int Count = -1;

                string result = string.Empty;

                string Url = "http://zx.bjmemc.com.cn/Charts/APIMap.aspx";

                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);

                request.Method = "GET";
                using (var stream = request.GetResponse().GetResponseStream())

                using (var reader = new StreamReader(stream, Encoding.UTF8))
                {
                    result = reader.ReadToEnd();
                }

                HtmlDocument doc = new HtmlDocument();

                doc.Load(new StringReader(result));

                HtmlNode root = doc.DocumentNode;

                List<string> anchorTags = new List<string>();


                foreach (HtmlNode link in root.SelectNodes("//div"))
                {
                    string att = link.InnerHtml;
                    anchorTags.Add(att);
                }

                for (int i = 0; i < anchorTags.Count; i++)
                {
                    if (anchorTags[i].Contains(">朝阳农展馆<"))
                    {
                        Count = i;
                    }
                }

                if (Count > -1)
                {
                    char[] delimiterChars = { ';', '(' };

                    string[] BPM10 = anchorTags[Count].Split(delimiterChars);
                    double bpmNumber = Convert.ToDouble(BPM10[5]) * 1000;

                    return bpmNumber.ToString();
                }
            }
            catch (Exception e)
            {
                return "";
            }
            return "";
        }

        private string GetBeijingPM25()
        {
            try
            {

                int Count = -1;

                string result = string.Empty;

                string Url = "http://zx.bjmemc.com.cn/Charts/PM25.aspx";

                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);

                request.Method = "GET";
                using (var stream = request.GetResponse().GetResponseStream())

                using (var reader = new StreamReader(stream, Encoding.UTF8))
                {
                    result = reader.ReadToEnd();
                }

                HtmlDocument doc = new HtmlDocument();

                doc.Load(new StringReader(result));

                HtmlNode root = doc.DocumentNode;

                List<string> anchorTags = new List<string>();


                foreach (HtmlNode link in root.SelectNodes("//tbody"))
                {
                    string att = link.InnerHtml;
                    anchorTags.Add(att);
                }

                string compareString = "10:00";

                for (int i = 0; i < anchorTags.Count; i++)
                {

                    char[] delimiterChars = { '日', '<' };

                    string[] BPM25 = anchorTags[i].Split(delimiterChars);

                    for (int k = 0; k < BPM25.Length; k++)
                    {

                        string trimString = BPM25[k].Trim();

                        if (trimString.Equals(compareString))
                        {
                            Count = i;
                        }
                    }
                }

                if (Count > -1)
                {
                    char[] delimiterChars = { '>', '日', '<' };

                    string[] BPM25 = anchorTags[Count].Split(delimiterChars);

                    double bpm25Number = Convert.ToDouble(BPM25[9].Trim()) * 1000;

                    return bpm25Number.ToString();
                }

            }
            catch (Exception e)
            {
                return "";
            }

            return "";
        }

        private string GetBeijingAPI()
        {
            try
            {
                int Count = -1;

                string result = string.Empty;

                string Url = "http://zx.bjmemc.com.cn/IndexAirReport/AirDailyReport.aspx";

                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);

                request.Method = "GET";
                using (var stream = request.GetResponse().GetResponseStream())

                using (var reader = new StreamReader(stream, Encoding.UTF8))
                {
                    result = reader.ReadToEnd();
                }

                HtmlDocument doc = new HtmlDocument();

                doc.Load(new StringReader(result));

                HtmlNode root = doc.DocumentNode;

                List<string> anchorTags = new List<string>();


                foreach (HtmlNode link in root.SelectNodes("//tbody"))
                {
                    string att = link.InnerHtml;
                    anchorTags.Add(att);
                }

                string compareString = "&nbsp;朝阳农展馆";

                for (int i = 0; i < anchorTags.Count; i++)
                {

                    char[] delimiterChars = { '>', '<' };

                    string[] BAPI = anchorTags[i].Split(delimiterChars);

                    for (int k = 0; k < BAPI.Length; k++)
                    {

                        string trimString = BAPI[k].Trim();

                        if (trimString.Equals(compareString))
                        {
                            Count = i;
                        }
                    }
                }

                if (Count > -1)
                {
                    char[] delimiterChars = { '>', '<' };

                    string[] BAPI = anchorTags[Count].Split(delimiterChars);
                    double bapiNumber = Convert.ToDouble(BAPI[8].Trim());
                    return bapiNumber.ToString();
                }
            }
            catch (Exception e)
            {
                return "";
            }

            return "";
        }

        private string GetGuangzhouPM25()
        {
            string result = string.Empty;

            string Url = "http://www.gzepb.gov.cn/comm/pm25.asp";

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);

            request.Method = "GET";
            using (var stream = request.GetResponse().GetResponseStream())

            using (var reader = new StreamReader(stream, Encoding.UTF8))
                result = reader.ReadToEnd();
            
            HtmlDocument doc = new HtmlDocument();

            doc.Load(new StringReader(result));

            HtmlNode root = doc.DocumentNode;

            List<string> anchorTags = new List<string>();


            foreach (HtmlNode link in root.SelectNodes("//tr"))
            {
                string att = link.InnerHtml;
                anchorTags.Add(att);
            }

            if (anchorTags.Count > 0)
            {
                char[] delimiterChars = { '>', '<' };
                string[] BAPI = anchorTags[8].Split(delimiterChars);
                return BAPI[10].Trim();
            }

            return "";
        }

        private string GetHongKongAPI()
        {
            string result = string.Empty;

            string Url = "http://www.epd-asg.gov.hk/eindex.html";

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);

            request.Method = "GET";
            using (var stream = request.GetResponse().GetResponseStream())

            using (var reader = new StreamReader(stream, Encoding.UTF8))
                result = reader.ReadToEnd();

            HtmlDocument doc = new HtmlDocument();

            doc.Load(new StringReader(result));

            HtmlNode root = doc.DocumentNode;

            List<string> anchorTags = new List<string>();


            foreach (HtmlNode link in root.SelectNodes("//tr"))
            {
                string att = link.InnerHtml;
                anchorTags.Add(att);
            }

            if (anchorTags.Count > 0)
            {
                char[] delimiterChars = { '>', '<' };
                string[] BAPI = anchorTags[32].Split(delimiterChars);
                return BAPI[16].Trim();
            }

            return "";
        }

        private string GetHongKongPM10()
        {
            int Count = -1;

            string result = string.Empty;

            string Url = "http://www.epd-asg.gov.hk/english/24pollu/Central.html";

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);

            request.Method = "GET";
            using (var stream = request.GetResponse().GetResponseStream())

            using (var reader = new StreamReader(stream, Encoding.UTF8))
            {
                result = reader.ReadToEnd();
            }

            HtmlDocument doc = new HtmlDocument();

            doc.Load(new StringReader(result));

            HtmlNode root = doc.DocumentNode;

            List<string> anchorTags = new List<string>();


            foreach (HtmlNode link in root.SelectNodes("//tr"))
            {
                string att = link.InnerHtml;
                anchorTags.Add(att);
            }

            string compareString = DateTime.Now.ToString("yyyy-MM-dd") + " 10:00";

            for (int i = 0; i < anchorTags.Count; i++)
            {

                char[] delimiterChars = { '>', '<' };

                string[] BPM25 = anchorTags[i].Split(delimiterChars);

                for (int k = 0; k < BPM25.Length; k++)
                {

                    string trimString = BPM25[k].Trim();

                    if (trimString.Equals(compareString))
                    {
                        Count = i;
                    }
                }
            }

            if (Count > -1)
            {
                char[] delimiterChars = { '>', '<' };

                string[] BPM25 = anchorTags[Count].Split(delimiterChars);

                return BPM25[12].Trim();
            }

            return ""; 
        }


    }
}
